package com.luck.emp.report.logic;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.luck.emp.report.qry.ReportQry;
import com.luck.emp.report.vo.BillVo;
import com.luck.emp.report.vo.OutinVo;
import com.luck.emp.report.vo.SaleVo;
import com.luck.emp.report.vo.StorageVo;
import com.luck.org.entity.SysDepartment;
import com.luck.pf.framework.dao.BaseJdbcDao;
import com.luck.pf.framework.logic.BaseLogic;

public class ReportLogic extends BaseLogic implements IReportLogic {

	private BaseJdbcDao jdbcDao;

	public BaseJdbcDao getJdbcDao() {
		return jdbcDao;
	}

	public void setJdbcDao(BaseJdbcDao jdbcDao) {
		this.jdbcDao = jdbcDao;
	}

	public  <T> T findStorage(ReportQry qry, Class<T> cls){
		StringBuffer sql = new StringBuffer();
		Map params = new HashMap();
		sql.append("SELECT D.DEPT_NAME  DEPTNAME, ");
		sql.append("       D.ID         DEPTID,         ");
		sql.append("       WH.V_WH_NAME WHNAME, ");
		sql.append("       B.V_WH_CODE      WHCODE, ");
		sql.append("       P.V_NAME         PRODUCTNAME, ");
		sql.append("       B.V_PRODUCT_CODE PRODUCTCODE, ");
		sql.append("       P.V_PROD_BARCODE PRODBARCODE, ");
		sql.append("       P.V_SKU          SKU, ");
		sql.append("       B.QTY  qty ,");
		sql.append("       PARAMS.PARAM_NAME MEASUNIT");
		sql.append("  FROM (SELECT T.V_WH_CODE, T.V_PRODUCT_CODE, NVL(SUM(T.N_QTY), 0) QTY ");
		sql.append("          FROM EMP_T_WH_BALANCE T ");
		sql.append("         GROUP BY T.V_WH_CODE, T.V_PRODUCT_CODE) B, ");
		
		sql.append("       EMP_T_WAREHOUSE WH, ");
		sql.append("       SYS_DEPARTMENT D, ");
		sql.append("       EMP_T_PROD_BASE P,SYS_PARAMS PARAMS");
		sql.append(" WHERE B.V_WH_CODE = WH.V_WH_CODE ");
		sql.append("   AND WH.N_DEPT_ID = D.ID ");
		sql.append("   AND B.V_PRODUCT_CODE = P.V_PRODUCT_CODE");
		sql.append(" AND PARAMS.PARAM_CODE = 'p_meas_unit' ");
		sql.append(" AND P.V_MEAS_UNIT = PARAMS.PARAM_VALUE ");
		if(StringUtils.isNotBlank(qry.getProductName()))
			sql.append(" and p.v_name like '%"+qry.getProductName()+"%' ");
		if(StringUtils.isNotBlank(qry.getProductCode())){
			sql.append(" and b.V_PRODUCT_CODE like :productCode ");
			params.put("productCode", "%"+qry.getProductCode()+"%");
		}
		if(StringUtils.isNotBlank(qry.getProdBarcode())){
			sql.append(" and p.V_PROD_BARCODE like :proBarcode ");
			params.put("proBarcode", "%"+qry.getProdBarcode()+"%");
		}
		if(StringUtils.isNotBlank(qry.getSku())){
			sql.append(" and p.V_SKU like :sku ");
			params.put("sku", "%"+qry.getSku()+"%");
		}
		if(StringUtils.isNotBlank(qry.getWhCode())){
			sql.append(" and b.V_WH_CODE =:whCode ");
			params.put("whCode", qry.getWhCode());
		}
		if(qry.getSearchProductType().equals("2"))
			sql.append(" and b.qty>0 ");
		
		if(qry.getDeptId() == null){
			sql.append(" and wh.N_DEPT_ID =:deptId ");
			params.put("deptId", qry.getUserInfo().getDeptId());
		}else{
			if(qry.getSearchLevel().equals("1")){//本级
				sql.append(" and wh.N_DEPT_ID =:deptId ");
				params.put("deptId", qry.getDeptId());
			}else if(qry.getSearchLevel().equals("2")){//本级及下级
				SysDepartment d = super.get(SysDepartment.class, qry.getDeptId());
				sql.append(" and d.tree_no like :treeNo ");
				params.put("treeNo", d.getTreeNo()+"%");
			}
		}
		sql.append(" order by d.id, b.v_product_code ");
		//System.out.println(sql.toString());
		if (cls.getName().equals("java.util.List")) {
			return (T)  this.jdbcDao.queryForList(sql.toString(), params,storageRw);
		} else {
			return (T) jdbcDao.queryForPage(sql.toString(), params, qry.getPageNo(),qry.getPageSize(), storageRw);
		}
	}
	private static final StorageRw storageRw = new StorageRw();

	private static final class StorageRw implements RowMapper {
		public Object mapRow(ResultSet rs, int rownum) throws SQLException {
			StorageVo vo = new StorageVo();
			vo.setIndex(rownum + 1);
			vo.setCurrentQry(rs.getLong("qty"));
			vo.setDeptId(rs.getLong("deptId"));
			vo.setDeptName(rs.getString("deptName"));
			vo.setProdBarcode(rs.getString("PRODBARCODE"));
			vo.setProductCode(rs.getString("productCode"));
			vo.setProductName(rs.getString("productName"));
			vo.setSku(rs.getString("sku"));
			vo.setWhCode(rs.getString("whCode"));
			vo.setWhName(rs.getString("whName"));
			vo.setMeasUnit(rs.getString("MEASUNIT"));
			//vo.setNote(rs.getString("V_NOTE"));
			//vo.setExDId(rs.getLong("N_EX_D_ID"));
			return vo;
		}
	}
	public  <T> T findSalePage(ReportQry qry, Class<T> cls){
		StringBuffer sql = new StringBuffer();
		Map params = new HashMap();
		//--计算销售单
		if(qry.getCountType().equals("1")){//按商品
			sql.append(" SELECT a.*,  BASE.V_NAME name_c, KIND.V_NAME parent_c, a.v_product_code code_c ");
			sql.append(" FROM ( ");
			sql.append(" SELECT D.V_PRODUCT_CODE , ");
		}else if(qry.getCountType().equals("2")){//按操作员
			sql.append(" SELECT a.*,  dept.DEPT_NAME parent_c, u.REALNAME name_c, u.LOGINNAME code_c ");
			sql.append(" FROM ( ");
			sql.append(" SELECT m.N_create_user user_id , ");
		}else if(qry.getCountType().equals("3")){//按机构
			sql.append(" SELECT a.*,  pdept.DEPT_NAME parent_c, dept.dept_name name_c, dept.dept_code code_c ");
			sql.append(" FROM ( ");
			sql.append(" SELECT dept.id dept_id , ");
		}else if(qry.getCountType().equals("4")){//按业务员
			sql.append(" SELECT a.*,  man.V_NAME name_c, dept.dept_name parent_c, man.V_SALE_CODE code_c ");
			sql.append(" FROM ( ");
			sql.append(" SELECT m.N_SALEMAN_ID N_SALEMAN_ID , ");
		}else if(qry.getCountType().equals("5")){//按客户
			sql.append(" SELECT a.*,  cust.V_CUST_NAME name_c, dept.dept_name parent_c, cust.V_CUST_CODE code_c ");
			sql.append(" FROM ( ");
			sql.append(" SELECT m.N_CUST_ID cust_id , ");
		}
		sql.append("        SUM(NVL(DECODE(M.V_BILL_TYPE, '21', D.N_QTY), 0) - ");
		sql.append("            NVL(DECODE(M.V_BILL_TYPE, '22', D.N_QTY), 0)) QTY, ");
		sql.append("        SUM(NVL(DECODE(M.V_BILL_TYPE, '21', D.N_PRICE * D.N_QTY), 0) - ");
		sql.append("            NVL(DECODE(M.V_BILL_TYPE, '22', D.N_PRICE * D.N_QTY), 0)) YJJE, ");
		sql.append("        SUM(NVL(DECODE(M.V_BILL_TYPE, '21', D.N_DISC_PRICE * D.N_QTY), 0) - ");
		sql.append("            NVL(DECODE(M.V_BILL_TYPE, '22', D.N_DISC_PRICE * D.N_QTY), 0)) ZKJE, ");
		sql.append("        SUM(NVL(DECODE(M.V_BILL_TYPE, '21', D.N_REAL_PRICE * D.N_QTY), 0) - ");
		sql.append("            NVL(DECODE(M.V_BILL_TYPE, '22', D.N_REAL_PRICE * D.N_QTY), 0)) SSJE, ");
		sql.append("        SUM(NVL(DECODE(M.V_BILL_TYPE, '21', D.N_REAL_PRICE * D.N_QTY - D.N_STD_PRICE * D.N_QTY),0) - ");
		sql.append("            NVL(DECODE(M.V_BILL_TYPE, '22', D.N_REAL_PRICE * D.N_QTY - D.N_STD_PRICE * D.N_QTY),0)) LRJE ");
		sql.append("   FROM EMP_T_SL_ORDER_D D, ");
		sql.append("        EMP_T_SL_ORDER_M M, ");
		sql.append("        SYS_DEPARTMENT   DEPT , EMP_T_PROD_BASE base ");
		sql.append("  WHERE D.V_SL_CODE = M.V_SL_CODE ");
		sql.append("    AND M.N_DEPT_ID = DEPT.ID  and d.v_product_code=base.v_product_code ");
		sql.append("    AND M.V_BILL_TYPE IN ('21', '22', '24')  ");//普通销售单，销售退货单，寄递销售单
		sql.append("    AND M.V_STATUS = '40' "); //状态
		//插入条件
		//机构过滤
		sql.append(" ");
		if(qry.getSearchLevel().equals("1")){//本级
			sql.append(" and m.N_DEPT_ID =:deptId ");
			params.put("deptId", qry.getDeptId());
		}else if(qry.getSearchLevel().equals("2")){//本级及下级
			SysDepartment d = super.get(SysDepartment.class, qry.getDeptId());
			sql.append(" and dept.tree_no like :treeNo ");
			params.put("treeNo", d.getTreeNo()+"%");
		}
		//分类
		if(qry.getKindId() != null){
			sql.append(" and base.n_prod_kind =:kindId ");
			params.put("kindId", qry.getKindId());
		}
		//支付方式
		if(StringUtils.isNotBlank(qry.getPayType())){
			sql.append(" and m.V_PAY_TYPE =:payType ");
			params.put("payType", qry.getPayType());
		}
		//商品条件

		if(StringUtils.isNotBlank(qry.getProductName())){
			sql.append(" and base.V_NAME like:productName ");
			params.put("productName", "%"+qry.getProductName()+"%");
		}
		if(StringUtils.isNotBlank(qry.getProductCode())){
			sql.append(" and base.V_PRODUCT_CODE like:productCode ");
			params.put("productCode", "%"+qry.getProductCode()+"%");
		}
		//开始时间
		if (qry.getStartDate() != null){
			//sql.append(" and m.D_CREATE_DATE >= to_date(:startDate,'yyyy-MM-dd hh24:mi:ss') ");
			sql.append(" and m.D_CREATE_DATE >=:startDate");
			params.put("startDate", qry.getStartDate());
		}
		
		//结束时间
		if (qry.getEndDate() != null){
			//sql.append(" and m.D_CREATE_DATE >= to_date(:startDate,'yyyy-MM-dd hh24:mi:ss') ");
			sql.append(" and m.D_CREATE_DATE <=:endDate");
			params.put("endDate", qry.getEndDate());
		}
		
		//分组条件
		if(qry.getCountType().equals("1")){//按商品
			sql.append("  GROUP BY D.V_PRODUCT_CODE ) a  ");
			sql.append(" ,EMP_T_PROD_BASE base,  emp_t_prod_kind kind");
			sql.append(" ");
			sql.append("  WHERE a.V_PRODUCT_CODE = base.v_product_code AND base.n_prod_kind=kind.n_id");
		}else if(qry.getCountType().equals("2")){//按操作员
			sql.append("  GROUP BY m.N_create_user ) a  ");
			sql.append(" , SYS_USER u,SYS_DEPARTMENT dept ");
			sql.append(" WHERE a.user_id= u.id and u.DEPT_ID=dept.id");
			
		}else if(qry.getCountType().equals("3")){//按机构
			sql.append("  GROUP BY dept.id ) a  ");
			sql.append(" , SYS_DEPARTMENT pdept ,SYS_DEPARTMENT dept ");
			sql.append(" WHERE a.dept_id= dept.id and dept.P_DEPT_ID=pdept.id");			
		}else if(qry.getCountType().equals("4")){//按业务员
			sql.append("  GROUP BY m.N_SALEMAN_ID ) a  ");
			sql.append(" , EMP_T_SALESMAN man ,SYS_DEPARTMENT dept ");
			sql.append(" WHERE a.N_SALEMAN_ID= man.N_ID and man.N_DEPT_ID=dept.id");			
		}else if(qry.getCountType().equals("5")){//按客户
			sql.append("  GROUP BY m.N_CUST_ID ) a  ");
			sql.append(" , EMP_T_CUST_INFO cust ,SYS_DEPARTMENT dept ");
			sql.append(" WHERE a.cust_id= cust.N_ID and cust.n_DEPT_ID=dept.id");			
		}
		//排序
		if(qry.getOrderByColumn().equals("2"))
			sql.append(" ORDER BY a.qty desc");
		else
			sql.append(" ORDER BY a.ssje desc");
		
		
		//System.out.println(sql.toString());
		if (cls.getName().equals("java.util.List")) {
			return (T)  this.jdbcDao.queryForList(sql.toString(), params,saleRw);
		} else {
			return (T) jdbcDao.queryForPage(sql.toString(), params, qry.getPageNo(),qry.getPageSize(), saleRw);
		}
	}
	private static final SaleRw saleRw = new SaleRw();

	private static final class SaleRw implements RowMapper {
		public Object mapRow(ResultSet rs, int rownum) throws SQLException {
			SaleVo vo = new SaleVo();
			vo.setIndex(rownum + 1);
			vo.setParentColumn(rs.getString("parent_c"));
			vo.setNameColumn(rs.getString("name_c"));
			vo.setCodeColumn(rs.getString("code_c"));
			vo.setQty(rs.getLong("qty"));
			vo.setYjje(rs.getDouble("yjje"));
			vo.setZkje(rs.getDouble("zkje"));
			vo.setSsje(rs.getDouble("ssje"));
			vo.setLrje(rs.getDouble("lrje"));
			return vo;
		}
	}
	public  List<OutinVo> findOutinqkPage(ReportQry qry){
		StringBuffer sql = new StringBuffer();
		Map params = new HashMap();
		sql.append("SELECT DEPT.DEPT_NAME DEPTNAME, ");
		sql.append("       WH.V_WH_NAME WHNAME, ");
		sql.append("       B.V_NAME PRODUCTNAME, a.V_PRODUCT_CODE productcode, ");
		sql.append("       K.V_NAME KINDNAME, B.V_PROD_BARCODE BARCODE, ");
		sql.append("       A.CGOUT + A.SLOUT + A.DBOUT + A.PDOUT + A.ASSOUT OUTALL, ");
		sql.append("       A.CGIN + A.DBIN + A.PDIN + A.ASSIN INALL,  C.QTY QTY, ");
		sql.append("       A.* ");
		sql.append("  FROM (SELECT M.N_IO_DEPT_ID, ");
		sql.append("               D.V_WH_CODE, ");
		sql.append("               D.V_PRODUCT_CODE, ");
		sql.append("               -NVL(SUM(DECODE(M.V_INOUT_TYPE, '41', D.N_QTY)), 0) CGOUT, "); 
		sql.append("               -NVL(SUM(DECODE(M.V_INOUT_TYPE, '42', D.N_QTY)), 0) SLOUT,  ");
		sql.append("               -NVL(SUM(DECODE(M.V_INOUT_TYPE, '44', D.N_QTY)), 0) DBOUT,   ");
		sql.append("               -NVL(SUM(DECODE(M.V_INOUT_TYPE, '46', D.N_QTY)), 0) PDOUT,    ");
		sql.append("               -NVL(SUM(DECODE(M.V_INOUT_TYPE, '47', D.N_QTY)), 0) ASSOUT,     ");
		sql.append("               NVL(SUM(DECODE(M.V_INOUT_TYPE, '11', D.N_QTY)), 0) CGIN,       ");
		sql.append("               NVL(SUM(DECODE(M.V_INOUT_TYPE, '12', D.N_QTY)), 0) DBIN,    ");
		sql.append("               NVL(SUM(DECODE(M.V_INOUT_TYPE, '15', D.N_QTY)), 0) PDIN,      ");
		sql.append("               NVL(SUM(DECODE(M.V_INOUT_TYPE, '16', D.N_QTY)), 0) ASSIN ");
		sql.append("          FROM EMP_T_WH_IO_D D, EMP_T_WH_IO_M M ");
		sql.append("         WHERE D.N_IO_ID = M.N_IO_ID ");
		//开始时间
		if (qry.getStartDate() != null){
			sql.append(" and d.D_IN_WH_DATE >=:startDate");
			params.put("startDate", qry.getStartDate());
		}
		//结束时间
		if (qry.getEndDate() != null){
			sql.append(" and d.D_IN_WH_DATE <=:endDate");
			params.put("endDate", qry.getEndDate());
		}
		sql.append("         GROUP BY M.N_IO_DEPT_ID, D.V_WH_CODE, D.V_PRODUCT_CODE) A, ");
		sql.append(" (SELECT T.V_WH_CODE, T.V_PRODUCT_CODE, NVL(SUM(T.N_QTY), 0) QTY FROM EMP_T_WH_BALANCE T  GROUP BY T.V_WH_CODE, T.V_PRODUCT_CODE) C,");
		sql.append("       SYS_DEPARTMENT DEPT, ");
		sql.append("       EMP_T_WAREHOUSE WH, ");
		sql.append("       EMP_T_PROD_BASE B, ");
		sql.append("       EMP_T_PROD_KIND K ");
		sql.append(" WHERE A.N_IO_DEPT_ID = DEPT.ID ");
		sql.append("   AND A.V_WH_CODE = WH.V_WH_CODE ");
		sql.append("   AND A.V_PRODUCT_CODE = B.V_PRODUCT_CODE ");
		sql.append("   AND B.N_PROD_KIND = K.N_ID ");
		sql.append("  AND A.V_WH_CODE = C.V_WH_CODE(+) AND A.V_PRODUCT_CODE = C.V_PRODUCT_CODE(+)");
		
		if(StringUtils.isNotBlank(qry.getProductName()))
			sql.append(" and b.v_name like '%"+qry.getProductName()+"%' ");
		if(StringUtils.isNotBlank(qry.getProductCode())){
			sql.append(" and b.V_PRODUCT_CODE like :productCode ");
			params.put("productCode", "%"+qry.getProductCode()+"%");
		}
		if(StringUtils.isNotBlank(qry.getProdBarcode())){
			sql.append(" and b.V_PROD_BARCODE like :proBarcode ");
			params.put("proBarcode", "%"+qry.getProdBarcode()+"%");
		}
		if(StringUtils.isNotBlank(qry.getSku())){
			sql.append(" and b.V_SKU like :sku ");
			params.put("sku", "%"+qry.getSku()+"%");
		}
		if(qry.getDeptId() == null){
			sql.append(" and a.N_IO_DEPT_ID =:deptId ");
			params.put("deptId", qry.getUserInfo().getDeptId());
		}else{
			if(qry.getSearchLevel().equals("1")){//本级
				sql.append(" and a.N_IO_DEPT_ID =:deptId ");				
			}else if(qry.getSearchLevel().equals("2")){//本级及下级
				SysDepartment d = super.get(SysDepartment.class, qry.getDeptId());
				sql.append(" and DEPT.tree_no like :treeNo ");
				params.put("treeNo", d.getTreeNo()+"%");
			}
		}
		sql.append(" order by a.N_IO_DEPT_ID, a.v_wh_code, a.V_PRODUCT_CODE");
		params.put("deptId", qry.getDeptId());
		//System.out.println(sql.toString());
		return this.jdbcDao.queryForList(sql.toString(), params,inoutRw);
	}
	private static final InoutRw inoutRw = new InoutRw();

	private static final class InoutRw implements RowMapper {
		public Object mapRow(ResultSet rs, int rownum) throws SQLException {
			OutinVo vo = new OutinVo();
			vo.setIndex(rownum + 1);
			vo.setDeptName(rs.getString("deptname"));
			vo.setWhName(rs.getString("whname"));
			vo.setKindName(rs.getString("kindname"));
			vo.setProductName(rs.getString("PRODUCTNAME"));
			vo.setProductCode(rs.getString("productcode"));
			vo.setProdBarcode(rs.getString("BARCODE"));

			vo.setCurrentStock(rs.getLong("qty"));
			
			vo.setOutAllCount(rs.getLong("OUTALL"));
			vo.setInAllCount(rs.getLong("INALL"));
			
			vo.setCgOut(rs.getLong("CGOUT"));
			vo.setSlOut(rs.getLong("SLOUT"));
			vo.setExOut(rs.getLong("DBOUT"));
			vo.setPdOut(rs.getLong("PDOUT"));
			vo.setAssOut(rs.getLong("ASSOUT"));
			
			vo.setCgIn(rs.getLong("CGIN"));
			vo.setExIn(rs.getLong("DBIN"));
			vo.setPdIn(rs.getLong("PDIN"));
			vo.setAssIn(rs.getLong("ASSIN"));
			return vo;
		}
	}
	public  List<BillVo> findBill(ReportQry qry){
		return null;
	}
}
